package dao;

import pojo.Manager;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ManagerDAOImpl {

    private static final String TABLE_NAME = "Manager";

    public static void add(Manager manager) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + " (manager_id, id_number) VALUES (?, ?)";
        JDBCUtils.execute(sql,null, manager.getIdNumber());
    }

    public static void delete(int managerID) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE manager_id = ?";
        JDBCUtils.execute(sql, managerID);
    }

    public static void update(Manager manager) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + " SET id_number = ? WHERE manager_id = ?";
        JDBCUtils.execute(sql, manager.getIdNumber(), manager.getManagerID());
    }

    public static Manager getById(int managerID) throws SQLException {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE manager_id = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, managerID);
        if (resultSet.next()) {
            return createManagerFromResultSet(resultSet);
        }
        return null;
    }

    public static List<Manager> getAll() throws SQLException {
        List<Manager> managers = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        while (resultSet.next()) {
            Manager manager = createManagerFromResultSet(resultSet);
            managers.add(manager);
        }
        return managers;
    }

    public static int getCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("count");
        }
        return 0;
    }

    public static List<Manager> getByPage(int start, int pageSize) throws SQLException {
        List<Manager> managers = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, start, pageSize);
        while (resultSet.next()) {
            Manager manager = createManagerFromResultSet(resultSet);
            managers.add(manager);
        }
        return managers;
    }

    public static List<Manager> findByKeyword(String keyword) throws SQLException {
        List<Manager> managers = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id_number LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%");
        while (resultSet.next()) {
            Manager manager = createManagerFromResultSet(resultSet);
            managers.add(manager);
        }
        return managers;
    }

    public static List<Manager> findByPageAndKeyword(int start, int pageSize, String keyword) throws SQLException {
        List<Manager> managers = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id_number LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", start, pageSize);
        while (resultSet.next()) {
            Manager manager = createManagerFromResultSet(resultSet);
            managers.add(manager);
        }
        return managers;
    }

    private static Manager createManagerFromResultSet(ResultSet resultSet) throws SQLException {
        int managerID = resultSet.getInt("manager_id");
        String idNumber = resultSet.getString("id_number");
        return new Manager(managerID, idNumber);
    }
}
